﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[fncBackupList]
#-- Purpose:		Returns a list of backups for a database, with recovery path
#--	Last Update:	05/30/2012
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE FUNCTION [dbo].[fncBackupList]
(	
	@database_name			sysname,
	@only_recovery_window	bit				= NULL,
	@start_date				datetime		= NULL,
	@label					nvarchar(255)	= NULL
)
RETURNS @returntable TABLE 
(
	id							int					PRIMARY KEY,
	recovery_path_broken		bit,
	backup_set_id				int,
	db_backup_set_id			int,
	prev_tlog_backup_set_id		int,
	database_name				nvarchar(128),
	name						nvarchar(128),
	type						char(1),
	backup_start_date			datetime,
	has_bulk_logged_data		bit,
	backup_size					numeric(20,0),
	physical_device_name		nvarchar(260),
	file_exists					bit,
	database_backup_lsn			numeric(25,0),
	differential_base_lsn		numeric(25,0),
	checkpoint_lsn				numeric(25,0),
	fork_point_lsn				numeric(25,0),
	first_lsn					numeric(25,0),
	last_lsn					numeric(25,0),
	first_recovery_fork_guid	uniqueidentifier,
	last_recovery_fork_guid		uniqueidentifier
)
AS
BEGIN
	--- Parameter Validation
	DECLARE @parms int
	SET @parms = 0

	IF @only_recovery_window IS NOT NULL
		SET @parms = @parms + 1
	IF @start_date IS NOT NULL
	  BEGIN
		SET @parms = @parms + 1

		--- Ensure a midnight timestamp on the date
		SET @start_date = DATEADD(DAY, 0, DATEDIFF(DAY, 0, @start_date))
	  END
	IF @label IS NOT NULL
		SET @parms = @parms + 1

	IF @parms > 1
	  BEGIN
		RETURN
	  END

	--- Declare Local Variables
	DECLARE		@recovery_window int, @first_backup_set_id int,
				@first_id int, @first_db_backup_set_id int
	DECLARE		@tBkp TABLE 
	(
		backup_set_id				int				PRIMARY KEY,
		db_backup_set_id			int,
		prev_tlog_backup_set_id		int,
		database_name				nvarchar(128),
		name						nvarchar(128),
		type						char(1),
		backup_start_date			datetime,
		has_bulk_logged_data		bit,
		backup_size					numeric(20,0),
		physical_device_name		nvarchar(260),
		file_exists					bit,
		database_backup_lsn			numeric(25,0),
		differential_base_lsn		numeric(25,0),
		checkpoint_lsn				numeric(25,0),
		fork_point_lsn				numeric(25,0),
		first_lsn					numeric(25,0),
		last_lsn					numeric(25,0),
		first_recovery_fork_guid	uniqueidentifier,
		last_recovery_fork_guid		uniqueidentifier
	)

	--- Get a list of backups
	IF ISNULL(@only_recovery_window, 0) = 1
	  BEGIN
		--- Determine the configured Recovery Windows Days
		SELECT		@recovery_window = ISNULL(cfg_recover_window_days, 8)
		FROM		dbo.tblConfiguration

		--- Default to 8 days if nothing is found in tblConfiguration
		IF @recovery_window IS NULL
			SET @recovery_window = 8

		--- Backups only from the recovery window
		INSERT INTO @tBkp (backup_set_id, database_backup_lsn, database_name)
			SELECT		bs.backup_set_id,
						database_backup_lsn = bs.database_backup_lsn,
						@database_name
			FROM		msdb.dbo.backupset bs (NOLOCK)
			WHERE		bs.database_name = @database_name
						AND bs.is_copy_only = 0
						AND bs.is_snapshot = 0
						AND bs.backup_start_date >= DATEADD(dd, (-1 * @recovery_window), GETDATE())

		--- Store the first backup_set_id of the recovery window						
		SELECT		@first_backup_set_id = backup_set_id
		FROM		(
					SELECT		TOP 1 *
					FROM		@tBkp
					ORDER BY	backup_start_date, 
								database_backup_lsn,
								checkpoint_lsn
					) t1

		--- Add Full Backup
		INSERT INTO @tBkp (backup_set_id, database_backup_lsn, database_name)
			SELECT		bs.backup_set_id,
						database_backup_lsn = bs.database_backup_lsn,
						@database_name
			FROM		msdb.dbo.backupset bs (NOLOCK)
			WHERE		bs.database_name = @database_name
						AND backup_set_id NOT IN (SELECT backup_set_id FROM @tBkp)
						AND (
							checkpoint_lsn IN (SELECT database_backup_lsn FROM @tBkp)
							OR database_backup_lsn IN (SELECT database_backup_lsn FROM @tBkp)
							)
						AND bs.is_copy_only = 0
						AND bs.is_snapshot = 0
	  END

	ELSE IF @start_date IS NOT NULL
	  BEGIN
		--- Backups only from the recovery window
		INSERT INTO @tBkp (backup_set_id, database_backup_lsn, database_name)
			SELECT		bs.backup_set_id,
						database_backup_lsn = bs.database_backup_lsn,
						@database_name
			FROM		msdb.dbo.backupset bs (NOLOCK)
			WHERE		bs.database_name = @database_name
						AND bs.is_copy_only = 0
						AND bs.is_snapshot = 0
						AND bs.backup_start_date >= @start_date
						AND bs.backup_start_date < DATEADD(dd, 1, @start_date)
	  END

	ELSE IF @label IS NOT NULL
	  BEGIN
		--- Backups with a specific label
		INSERT INTO @tBkp (backup_set_id, database_backup_lsn, database_name)
			SELECT		bs.backup_set_id,
						database_backup_lsn = bs.database_backup_lsn,
						@database_name
			FROM		msdb.dbo.backupset bs (NOLOCK)
			WHERE		bs.database_name = @database_name
						AND bs.is_copy_only = 0
						AND bs.is_snapshot = 0
						AND bs.name = @label
	  END

	ELSE
	  BEGIN
		--- All known backups
		INSERT INTO @tBkp (backup_set_id, database_backup_lsn, database_name)
			SELECT		bs.backup_set_id,
						database_backup_lsn = bs.database_backup_lsn,
						@database_name
			FROM		msdb.dbo.backupset bs (NOLOCK)
			WHERE		bs.database_name = @database_name
						AND bs.is_copy_only = 0
						AND bs.is_snapshot = 0
	  END
			
	--- Grab missing LSN and GUID values from the remaining records
	---		NOTE: this is faster than including this info in the query above!
	UPDATE		t1
	SET			differential_base_lsn = bs.differential_base_lsn,
				checkpoint_lsn = bs.checkpoint_lsn,
				fork_point_lsn = bs.fork_point_lsn,
				first_lsn = bs.first_lsn,
				last_lsn = bs.last_lsn,
				first_recovery_fork_guid = bs.first_recovery_fork_guid,
				last_recovery_fork_guid = bs.last_recovery_fork_guid,
				name = bs.name,
				type = bs.type,
				backup_start_date = bs.backup_start_date,
				has_bulk_logged_data = bs.has_bulk_logged_data,
				backup_size = bs.backup_size,
				physical_device_name = mf.physical_device_name
	FROM		@tBkp t1
	JOIN		msdb.dbo.backupset bs ON t1.backup_set_id = bs.backup_set_id
	JOIN		msdb.dbo.backupmediafamily mf (NOLOCK) ON bs.media_set_id = mf.media_set_id
	
	--- Determine the db_backup_set_id of differential backups, and if the file exists on disk
	UPDATE	bkp
	SET		file_exists = dbo.FileExists(physical_device_name),
			db_backup_set_id =			(
										SELECT		MAX(backup_set_id)
										FROM		@tBkp
										WHERE		backup_start_date <= bkp.backup_start_date
													AND type = 'D' 
													AND bkp.type = 'I'
													AND checkpoint_lsn = bkp.database_backup_lsn
										)
	FROM	@tBkp bkp
	WHERE	type IN ('D', 'I')

	--- Remove differential backups that belong to broken recovery chains
	DELETE	FROM @tBkp
	WHERE	type IN ('I')
			AND db_backup_set_id IS NULL

	--- Determine the db_backup_set_id and prev_tlog_backup_set_id for transaction logs, and if the file exists on disk
	UPDATE	bkp
	SET		file_exists = dbo.FileExists(physical_device_name),
			db_backup_set_id =			(
										SELECT		MAX(backup_set_id)
										FROM		@tBkp
										WHERE		backup_start_date <= bkp.backup_start_date
													AND (
														(
															type = 'D' 
															AND bkp.type = 'I'
															AND checkpoint_lsn = bkp.database_backup_lsn
														)
														OR (
															(type IN ('D', 'I') AND bkp.type = 'L')
															AND (
																(
																	bkp.fork_point_lsn IS NOT NULL
																	AND (
																			(
																				last_lsn <= bkp.fork_point_lsn
																				AND last_recovery_fork_guid = bkp.first_recovery_fork_guid
																			)
																			OR	(
																				last_lsn > bkp.fork_point_lsn
																				AND last_recovery_fork_guid = bkp.last_recovery_fork_guid
																			)
																		)
																)
																OR (
																	bkp.fork_point_lsn IS NULL
																	AND backup_start_date < bkp.backup_start_date
																	AND last_lsn <= bkp.last_lsn
																	AND checkpoint_lsn <= bkp.checkpoint_lsn
																	AND NOT (
																		last_lsn = bkp.last_lsn
																		AND checkpoint_lsn = bkp.checkpoint_lsn
																	)
																)
															)
														)
													)
										),
			prev_tlog_backup_set_id =	(
										SELECT		MAX(backup_set_id)
										FROM		@tBkp
										WHERE		database_name = bkp.database_name
													AND backup_set_id <> bkp.backup_set_id
													AND last_lsn = bkp.first_lsn
													AND backup_start_date <= bkp.backup_start_date
													AND type = 'L'
										)
	FROM	@tBkp bkp
	WHERE	type = 'L'

	--- Populate the Return Table
	INSERT INTO @returntable
		SELECT		ROW_NUMBER() OVER (ORDER BY	backup_start_date, 
												database_backup_lsn,
												checkpoint_lsn
						) id,
					NULL recovery_path_broken,
					bkp.*
		FROM		@tBkp bkp
		ORDER BY	backup_start_date,
					database_backup_lsn,
					checkpoint_lsn

	--- Remove backups that are older than the recovery window and unnecessary to the restore
	IF ISNULL(@only_recovery_window, 0) = 1
	  BEGIN
		--- Get information about the first recovery path backup
		SELECT	@first_id = id, 
				@first_db_backup_set_id = db_backup_set_id
		FROM	@returntable
		WHERE	backup_set_id = @first_backup_set_id
		
		--- Remove the transaction log backups older than the recovery path required differential
		DELETE	@returntable
		WHERE	id < @first_id
				AND db_backup_set_id <> @first_db_backup_set_id
				AND backup_set_id <> @first_db_backup_set_id
				AND type IN ('L', 'I')
	  END


	--- Only mark a broken recovery path if it is the recovery path or all files
	IF @start_date IS NULL AND @label IS NULL
	  BEGIN
		--- Determine if the recovery path is broken for any single backup file
		UPDATE		bkp
		SET			recovery_path_broken =
					(CASE	WHEN	bkp.type = 'D'
									OR ( bkp.type = 'I' AND bkp_db.backup_set_id IS NOT NULL )
									OR (
										bkp.type = 'L'
										AND (
											EXISTS (
												SELECT		TOP 1 * 
												FROM		@returntable 
												WHERE		backup_set_id = bkp.prev_tlog_backup_set_id
											) 
											OR (bkp_db.id < bkp.id - 1 AND bkp_db.last_lsn >= bkp.first_lsn)
										)
									)
							THEN 0 
							ELSE 1 END)
		FROM		@returntable bkp
		LEFT JOIN	@returntable bkp_db ON bkp_db.backup_set_id = bkp.db_backup_set_id

		--- If a differential has a broken recovery path, every other tlog after it until a
		---		full backup has a broken recovery path
		UPDATE		bkp1
		SET			recovery_path_broken = 1
		FROM		@returntable bkp
		JOIN		@returntable bkp1 ON bkp.backup_set_id = bkp1.db_backup_set_id
					AND bkp1.id > bkp.id
					AND bkp1.type = 'L'
					AND bkp1.recovery_path_broken = 0
		WHERE		bkp.recovery_path_broken = 1
					AND bkp.type = 'I'
				
		--- If a tlog has a broken recovery path, every other tlog after it until a
		---		full or differential backup has a broken recovery path
		UPDATE		bkp1
		SET			recovery_path_broken = 1
		FROM		@returntable bkp
		JOIN		@returntable bkp1 ON ISNULL(bkp.db_backup_set_id, 0) = ISNULL(bkp1.db_backup_set_id, 0)
					AND (bkp1.id > bkp.id OR (bkp1.id IS NULL AND bkp.id IS NULL))
					AND bkp1.type = 'L'
					AND bkp1.recovery_path_broken = 0
		WHERE		bkp.recovery_path_broken = 1
					AND bkp.type = 'L'
	  END

	RETURN
END